# -*- coding: utf-8 -*-
'''
Created on 2013-4-11

@author: cl.lam
'''
import os
from datetime import datetime as dt
import traceback, json
from flask import current_app as app
from flask import g, render_template, flash, session, redirect, url_for, request, jsonify, Response
from flask.blueprints import Blueprint
from flask.helpers import send_file
from sqlalchemy.sql.expression import and_, not_, or_
from sqlalchemy.orm.exc import NoResultFound

import random
from openpyxl.workbook import Workbook
from openpyxl.cell import get_column_letter
from openpyxl.style import Color, Border, Fill, Alignment


from sys2do.views import BasicView
from sys2do.util.decorator import templated, loginRequired, activetab, \
    allPermission, mypaginate, myjson
from sys2do.model import db, qry, InventoryLocation, InventoryProduct, InventoryNote, \
    ProductShap, ProductSeries, ProductType, Product, SysLog, Item, InventoryNoteDtl, InventoryNoteDtlItem
from sys2do.util.common import _g, _gld, _gl, _gp
from sys2do.util.logic_helper import getCurrentShopID, getOption, \
    getCurrentShopProfile
from sys2do.constant import MSG_SAVE_SUCC, TAB_FLAG, \
    MSG_NOT_ENOUGH_PARAMS, MESSAGE_ERROR, ACTIVE, MSG_UPDATE_SUCC, MESSAGE_INFO, \
    MSG_SERVER_ERROR, TAB_HOME, MSG_NO_ID_SUPPLIED, IVTNT_IN, IVTNT_APPROVE, \
    IVTNT_OUT, LOG_TYPE_UPDATE, IVTNT_INTERNAL, MSG_NOT_ALL_PARAMS_OK
from sys2do.util.exception import CustomizedExp, NoRelatedItemExp, \
    ItemNoInCorrectInventoryExp, ItemQtyNotMatchExp
from sys2do.setting import TMP_FOLDER



__all__ = ['bpIvt']

bpIvt = Blueprint( 'bpIvt', __name__ )
@bpIvt.before_request
def addActivetab():    session[TAB_FLAG] = TAB_HOME


class InventoryView( BasicView ):

    template_folder = "ivt"


    @templated( 'index.html' )
    @mypaginate( 'result' )
    @allPermission( ['INVENTORY_VIEW', ] )
    def index( self ):
        spobj = getCurrentShopProfile()

        if request.method == 'POST':
            form = SrhForm( request.form )
            session[url_for( '.view', action = 'index' )] = form
        else:
            form = session.get( url_for( '.view' ), SrhForm( request.form ) )
        form.url = url_for( '.view', action = 'index' )
        form.ivtID.choices = [( '', '' )] + [( ivt.id, ivt.fullPath ) for ivt in spobj.getIvts()]
        form.typeID.choices = getOption( ProductType, [ProductType.active == 0, ], ProductType.name )
        form.seriesID.choices = getOption( ProductSeries, [ProductSeries.active == 0, ], ProductSeries.name )
        form.shapID.choices = getOption( ProductShap, [ProductShap.active == 0, ], ProductShap.name )

        cds = [ InventoryProduct.active == ACTIVE , InventoryProduct.qty > 0,
                Product.active == ACTIVE ,
                Product.id == InventoryProduct.pdtID, InventoryLocation.id == InventoryProduct.ivtID,
                InventoryLocation.referID == spobj.shopID,
                ]
        if form.no.data : cds.append( Product.no.like( '%%%s%%' % form.no.data ) )
        if form.barcode.data : cds.append( Product.barcode.like( '%%%s%%' % form.barcode.data ) )
        if form.name.data : cds.append( Product.name.like( '%%%s%%' % form.name.data ) )
        if form.styleNo.data : cds.append( Product.styleNo.like( '%%%s%%' % form.styleNo.data ) )
        if form.certificate.data : cds.append( Product.certificate.like( '%%%s%%' % form.certificate.data ) )
        if form.clarity.data : cds.append( Product.clarity.like( '%%%s%%' % form.clarity.data ) )
        if form.color.data : cds.append( Product.color.like( '%%%s%%' % form.color.data ) )
        if form.goldFineness.data : cds.append( Product.goldFineness.like( '%%%s%%' % form.goldFineness.data ) )

        if _g( 'typeID', None ) : cds.append( Product.typeID == _g( 'typeID' ) )
        if _g( 'seriesID', None ): cds.append( Product.seriesID == _g( 'seriesID' ) )
        if _g( 'shapID', None ): cds.append( Product.shapID == _g( 'shapID' ) )
        if _g( 'ivtID', None ): cds.append( InventoryProduct.ivtID == _g( 'ivtID' ) )

        result = {}
        for ( pdt, ivt, ip ) in qry( Product, InventoryLocation, InventoryProduct ).filter( and_( *cds ) ).order_by( Product.name ):
            if pdt.id not in result :
                result[pdt.id] = {'product' : pdt, 'data' : [( ivt, ip ), ],
                                  'totalQty' : ip.qty, 'totalAvailableQty' : ip.availableQty, 'totalFutureQty' : ip.futureQty}
            else:
                result[pdt.id]['data'].append( ( ivt, ip ) )
                result[pdt.id]['totalQty'] += ip.qty
                result[pdt.id]['totalAvailableQty'] += ip.availableQty
                result[pdt.id]['totalFutureQty'] += ip.futureQty


        return {'result' : result.values(), 'form' : form}

    @templated( "add.html" )
    @loginRequired
    def add( self ):
        sid = getCurrentShopID()
        if request.method == 'GET':
            return {'parents' : InventoryLocation.listTree()}
        try:
            kw = request.form.to_dict()
            ivt = InventoryLocation.saveNew( referID = sid, **kw )
            if ivt.parentID:
                ivt.fullPathIDs = '%s|%s' % ( ivt.parent.fullPathIDs, ivt.id )
                ivt.fullPath = '%s%s' % ( ivt.parent.fullPath, ivt.name )
            else:
                ivt.fullPathIDs = ivt.id
                ivt.fullPath = ivt.name
            ivt = ivt.setLeaf( refer_id = sid )
            db.commit()
            flash( MSG_SAVE_SUCC, MESSAGE_INFO )
        except:
            db.rollback()
            flash( MSG_SERVER_ERROR, MESSAGE_ERROR )
            self._error( traceback.format_exc() )
        return redirect( url_for( '.view' ) )


    @templated( "update.html" )
    @loginRequired
    def update( self ):
        sid = getCurrentShopID()
        if request.method == 'GET':
            obj = InventoryLocation.get( _g( 'id' ) )
            '''
            ps = qry( InventoryLocation ).filter( and_( InventoryLocation.active == 0 ,
                                                    InventoryLocation.referID == sid,
                                                    not_( InventoryLocation.fullPathIDs.like( '%s%%' % obj.fullPathIDs ) ),
                                                    ) )
            '''
            return {'parents' : InventoryLocation.listTree(), 'obj' : obj}
        try:
            ivt = InventoryLocation.get( _g( 'id' ) )
            kw = request.form.to_dict()
            if kw.get( 'parentID', None ) and kw.get( 'parentID' ) != ivt.parentID:
                ivt.moveLeaf( kw['parentID'], refer_id = sid )
            ivt = ivt.saveEdit( **kw )
            data = [ivt]
            data.extend( InventoryLocation.listTree( ivt.id ) )
            for i in data:
                if i.parentID:
                    i.fullPathIDs = '%s|%s' % ( i.parent.fullPathIDs, i.id )
                    i.fullPath = '%s%s' % ( i.parent.fullPath, i.name )
                else:
                    i.fullPathIDs = i.id
                    i.fullPath = i.name
            db.commit()
            flash( MSG_UPDATE_SUCC, MESSAGE_INFO )
        except:
            self._error( traceback.format_exc() )
            db.rollback()
            flash( MSG_SERVER_ERROR, MESSAGE_ERROR )
            self._error( traceback.format_exc() )
        return redirect( url_for( '.view' ) )


    '''
    @templated( 'view_product.html' )
    @mypaginate( 'result' )
    @allPermission( ['INVENTORY_VIEW', ] )
    def viewProduct( self ):
        ivtid = _g( 'id' )
        if not ivtid :
            flash( MSG_NOT_ENOUGH_PARAMS, MESSAGE_ERROR )
            return redirect( url_for( '.view' ) )

        if request.method == 'POST':
            form = SrhForm( request.form )
            session[url_for( '.view', action = 'viewProduct', id = ivtid )] = form
        else:
            form = session.get( url_for( '.view' ), SrhForm( request.form ) )
        form.url = url_for( '.view', action = 'viewProduct', id = ivtid )
        form.typeID.choices = getOption( ProductType, [ProductType.active == 0, ], ProductType.name )
        form.seriesID.choices = getOption( ProductSeries, [ProductSeries.active == 0, ], ProductSeries.name )
        form.shapID.choices = getOption( ProductShap, [ProductShap.active == 0, ], ProductShap.name )

        obj = InventoryLocation.get( ivtid )
        cds = [ InventoryProduct.active == ACTIVE , InventoryProduct.ivtID == ivtid, InventoryProduct.qty > 0,
                Product.active == ACTIVE , Product.id == InventoryProduct.pdtID,
                ]
        if form.no.data : cds.append( Product.no.like( '%%%s%%' % form.no.data ) )
        if form.barcode.data : cds.append( Product.barcode.like( '%%%s%%' % form.barcode.data ) )
        if form.name.data : cds.append( Product.name.like( '%%%s%%' % form.name.data ) )
        if form.styleNo.data : cds.append( Product.styleNo.like( '%%%s%%' % form.styleNo.data ) )
        if form.certificate.data : cds.append( Product.certificate.like( '%%%s%%' % form.certificate.data ) )
        if form.clarity.data : cds.append( Product.clarity.like( '%%%s%%' % form.clarity.data ) )
        if form.color.data : cds.append( Product.color.like( '%%%s%%' % form.color.data ) )
        if form.goldFineness.data : cds.append( Product.goldFineness.like( '%%%s%%' % form.goldFineness.data ) )

        if _g( 'typeID', None ) : cds.append( Product.typeID == _g( 'typeID' ) )
        if _g( 'seriesID', None ): cds.append( Product.seriesID == _g( 'seriesID' ) )
        if _g( 'shapID', None ): cds.append( Product.shapID == _g( 'shapID' ) )

        result = qry( Product, InventoryProduct ).filter( and_( *cds ) ).order_by( Product.name )
        return {'obj' : obj , 'result' : result, 'form' : form}
    '''


    '''
    @templated( 'update_qty.html' )
    @allPermission( ['INVENTORY_EDIT'] )
    def updateQty( self ):
        piids = _gl( 'pdt_ivt' )
        if not piids :
            flash( MSG_NOT_ENOUGH_PARAMS, MESSAGE_ERROR )
            return redirect( url_for( '.view' ) )
        if type( piids ) != list : piids = [piids, ]
        cds = []
        for piid in piids:
            pid, iid = piid.split( "_" )
            cds.append( and_( InventoryProduct.pdtID == pid, InventoryProduct.ivtID == iid ) )
        result = qry( Product, InventoryProduct ).filter( and_( Product.active == ACTIVE, Product.id == InventoryProduct.pdtID ) ).filter( or_( *cds ) ).order_by( Product.name )
        return {'result' : result}

    @allPermission( ['INVENTORY_EDIT'] )
    def saveUpdateQty( self ):
        qty = _gp( 'qty_' )
        if not qty:
            flash( MSG_NOT_ENOUGH_PARAMS, MESSAGE_ERROR )
            return redirect( url_for( '.view' ) )
        try:
            for ( qk, qv )in qty:
                flag, qid, iid = qk.split( "_" )
                obj = qry( InventoryProduct ).filter( and_( InventoryProduct.pdtID == qid, InventoryProduct.ivtID == iid ) ).with_lockmode( 'update' ).one()
                obj.qty = int( qv )
                if obj.qty <= 0 : db.delete( obj )
            db.commit()
            flash( MSG_UPDATE_SUCC, MESSAGE_INFO )
        except:
            self._error( traceback.format_exc() )
            db.rollback()
            flash( MSG_SERVER_ERROR, MESSAGE_ERROR )

        return redirect( url_for( '.view' ) )
    '''


    @templated( 'internal.html' )
    def internalCreate( self ):
        piids = _gl( 'pdt_ivt' )
        if not piids :
            flash( MSG_NOT_ENOUGH_PARAMS, MESSAGE_ERROR )
            return redirect( url_for( '.view' ) )

        spobj = getCurrentShopProfile()

        if type( piids ) != list : piids = [piids, ]
        cds = []
        relatedItems = {}
        for piid in piids:
            pid, iid = piid.split( "_" )
            cds.append( and_( InventoryProduct.pdtID == pid, InventoryProduct.ivtID == iid ) )
            relatedItems[piid] = qry( Item ).filter( and_( Item.pdtID == pid, Item.ivtID == iid ) )

        result = qry( Product, InventoryProduct ).filter( and_( Product.active == ACTIVE, Product.id == InventoryProduct.pdtID ) ).filter( or_( *cds ) ).order_by( Product.name )
        ivts = qry( InventoryLocation ).filter( and_( InventoryLocation.active == ACTIVE, InventoryLocation.referID == spobj.shopID ) ).order_by( InventoryLocation.fullPathIDs )
        return {'result' : result, 'ivts' : ivts , 'items' : relatedItems}


    def internalSave( self ):
        try:
            spobj = getCurrentShopProfile()
            hdr = InventoryNote.create( {'shopID' : spobj.shopID, 'direction' : IVTNT_INTERNAL , 'ivtID' : None,
                                        'type' : None, 'status' : IVTNT_APPROVE , 'appTime' : dt.now()} )
            db.add( hdr )
            itemList = []
            for k, v in _gp( 'qty_' ):
                n, pdtID, ivtID = k.split( "_" )

                divtid = _g( 'divt_%s_%s' % ( pdtID, ivtID ) )
                if not divtid : continue
                qty = int( v )

                #===============================================================
                # 1. going to move the product
                # 2. do the actual move
                #===============================================================
#                 InventoryProduct.goingToMove( ivtID, divtid, pdtID, qty )
                InventoryProduct.toMove( ivtID, divtid, pdtID, qty )

                sIvt = InventoryLocation.get( ivtID )
                dIvt = InventoryLocation.get( divtid )
                pdt = Product.get( pdtID )

                dtl = InventoryNoteDtl.create( {'hdr' : hdr, 'pdt' : pdt, 'qty' : qty, 'sIvtLtn' : sIvt, 'dIvtLtn' : dIvt} )
                dtl.copyPdtInfo( pdt )
                db.add( dtl )
                dtlItemList = []
                items = _gl( 'items_%s_%s' % ( pdtID, ivtID ) )
                if not items : raise NoRelatedItemExp()
                if type( items ) != list : items = [items, ]
                if len( items ) != qty : raise ItemQtyNotMatchExp()
                for item in qry( Item ).filter( Item.id.in_( items ) ) :
                    if item.ivtID != sIvt.id : raise ItemNoInCorrectInventoryExp()    # the item no in this inventory
                    item.ivtID = divtid    # change the item's location
                    db.add( InventoryNoteDtlItem.create( {'hdr' : hdr, 'dtl' : dtl, 'item' : item} ) )    # make the realted item history
                    dtlItemList.append( item.no )
                dtl.itemList = dtlItemList or None    # match back the related item to the dtl
                db.add( SysLog( refClz = sIvt.__class__.__name__, type = LOG_TYPE_UPDATE, refID = sIvt.id, remark = u'转出商品 [%s],数量 [%s] 到仓位 [%s]。' % ( pdt.no, qty, sIvt.fullPath ) ) )
                db.add( SysLog( refClz = dIvt.__class__.__name__, type = LOG_TYPE_UPDATE, refID = dIvt.id, remark = u'从仓位 [%s]转入商品 [%s],数量 [%s]。' % ( dIvt.fullPath, pdt.no, qty ) ) )

            hdr.refer = hdr.no
#             hdr.itemList = itemList
            db.commit()
            flash( MSG_UPDATE_SUCC, MESSAGE_INFO )
        except CustomizedExp as e:
            db.rollback()
            flash( unicode( e ), MESSAGE_ERROR )
        except:
            self._error( traceback.format_exc() )
            db.rollback()
            flash( MSG_SERVER_ERROR, MESSAGE_ERROR )
        return redirect( url_for( '.view' ) )



    @templated( 'view_note.html' )
    def viewNote( self ):
        ivtid = _g( 'id' )
        obj = InventoryLocation.get( ivtid )
        result = qry( InventoryNote ).filter( and_( InventoryNote.active == 0, InventoryNote.ivtID == ivtid ) ).order_by( InventoryNote.createTime )
        return {'obj' : obj, 'result' : result}


    @myjson()
    def ajaxSrhItems( self ):
        ps = _gld( 'pdtID', 'ivtID' )
        if not ps['pdtID'] or not ps['ivtID'] : return {'code' : 1 , 'msg' : MSG_NOT_ALL_PARAMS_OK, 'data' : []}

        pdt, ivt = Product.get( ps['pdtID'] ), InventoryLocation.get( ps['ivtID'] )
        items = qry( Item ).filter( and_( Item.pdtID == ps['pdtID'], Item.ivtID == ps['ivtID'] ) ).order_by( Item.id )

        return {'code' : 0 , 'msg' : None ,
                'pdt' : unicode( pdt ), 'ivt' : unicode( ivt ),
                'data' : map( unicode, items ),
                }


    def export( self ):
        spobj = getCurrentShopProfile()

        cds = [ InventoryProduct.active == ACTIVE , InventoryProduct.qty > 0,
                Product.active == ACTIVE ,
                Product.id == InventoryProduct.pdtID, InventoryLocation.id == InventoryProduct.ivtID,
                InventoryLocation.referID == spobj.shopID,
                ]
        if _g( 'no' ) : cds.append( Product.no.like( '%%%s%%' % _g( 'no' ) ) )
        if _g( 'barcode' ) : cds.append( Product.barcode.like( '%%%s%%' % _g( 'barcode' ) ) )
        if _g( 'name' ) : cds.append( Product.name.like( '%%%s%%' % _g( 'name' ) ) )
        if _g( 'styleNo' ) : cds.append( Product.styleNo.like( '%%%s%%' % _g( 'styleNo' ) ) )
        if _g( 'certificate' ): cds.append( Product.certificate.like( '%%%s%%' % _g( 'certificate' ) ) )
        if _g( 'clarity' ) : cds.append( Product.clarity.like( '%%%s%%' % _g( 'clarity' ) ) )
        if _g( 'color' ): cds.append( Product.color.like( '%%%s%%' % _g( 'color' ) ) )
        if _g( 'goldFineness' ) : cds.append( Product.goldFineness.like( '%%%s%%' % _g( 'goldFineness' ) ) )

        if _g( 'typeID', None ) : cds.append( Product.typeID == _g( 'typeID' ) )
        if _g( 'seriesID', None ): cds.append( Product.seriesID == _g( 'seriesID' ) )
        if _g( 'shapID', None ): cds.append( Product.shapID == _g( 'shapID' ) )
        if _g( 'ivtID', None ): cds.append( InventoryProduct.ivtID == _g( 'ivtID' ) )

        result = {}
        for ( pdt, ivt, ip ) in qry( Product, InventoryLocation, InventoryProduct ).filter( and_( *cds ) ).order_by( Product.name ):
            if pdt.id not in result :
                result[pdt.id] = {'product' : pdt, 'data' : [( ivt, ip ), ],
                                  'totalQty' : ip.qty, 'totalAvailableQty' : ip.availableQty, }
            else:
                result[pdt.id]['data'].append( ( ivt, ip ) )
                result[pdt.id]['totalQty'] += ip.qty
                result[pdt.id]['totalAvailableQty'] += ip.availableQty

        header = [u'商品编号', u'商品名称', u'所处仓位', u'现存数量', u'可用数量', u'现存总数量', u'可用总数量', ]
        if not os.path.exists( TMP_FOLDER ) : os.makedirs( TMP_FOLDER )
        fileName = 'shop_stock_%s%s.xlsx' % ( dt.now().strftime( "%Y%m%d%H%M" ), random.randint( 100, 999 ) )
        filePath = os.path.join( TMP_FOLDER, fileName )

        wb = Workbook()
        ws = wb.create_sheet( 0 )

        row, col, = 1, 1
        for index, h in enumerate( header ) :
            _cell = ws.cell( "%s%s" % ( get_column_letter( col + index ), row ) )
            _cell.set_value_explicit( h )
            _cell.style.font.color.index = Color.WHITE
            _cell.style.font.name = 'Arial'
            _cell.style.font.bold = True
            _cell.style.font.size = 14

            _cell.style.borders.right.border_style = Border.BORDER_THIN
            _cell.style.borders.top.border_style = Border.BORDER_THIN
            _cell.style.borders.bottom.border_style = Border.BORDER_THIN

            _cell.style.fill.fill_type = Fill.FILL_SOLID
            _cell.style.fill.start_color.index = Color.DARKGREEN
            _cell.style.alignment.horizontal = Alignment.HORIZONTAL_CENTER
            _cell.style.alignment.vertical = Alignment.VERTICAL_CENTER


        row += 1
        for v in result.values() :
            pdt = v['product']
            data = v['data']

            tmpCol = col
            ws.merge_cells( '%s%s:%s%s' % ( get_column_letter( tmpCol ), row, get_column_letter( tmpCol ), row + len( data ) - 1 ) )
            ws.cell( "%s%s" % ( get_column_letter( tmpCol ), row ) ).set_value_explicit( pdt.no )

            tmpCol = col + 1
            ws.merge_cells( '%s%s:%s%s' % ( get_column_letter( tmpCol ), row, get_column_letter( tmpCol ), row + len( data ) - 1 ) )
            ws.cell( "%s%s" % ( get_column_letter( tmpCol ), row ) ).set_value_explicit( pdt.name )

            tmpCol = col + 5
            ws.merge_cells( '%s%s:%s%s' % ( get_column_letter( tmpCol ), row, get_column_letter( tmpCol ), row + len( data ) - 1 ) )
            ws.cell( "%s%s" % ( get_column_letter( tmpCol ), row ) ).set_value_explicit( unicode( v['totalQty'] ) )

            tmpCol = col + 6
            ws.merge_cells( '%s%s:%s%s' % ( get_column_letter( tmpCol ), row, get_column_letter( tmpCol ), row + len( data ) - 1 ) )
            ws.cell( "%s%s" % ( get_column_letter( tmpCol ), row ) ).set_value_explicit( unicode( v['totalAvailableQty'] ) )

            for ( ivt, ip ) in data :
                ws.cell( "C%s" % row ).set_value_explicit( ivt.fullPath )
                ws.cell( "D%s" % row ).set_value_explicit( unicode( ip.qty ) )
                ws.cell( "E%s" % row ).set_value_explicit( unicode( ip.availableQty ) )
                row += 1

        for c in ['A', 'B', 'C', 'D', 'E', 'F', 'G' ]:        ws.column_dimensions[c].width = 20    # set the no column's width
        wb.save( filePath )
        return send_file( filePath, as_attachment = True, attachment_filename = fileName )



bpIvt.add_url_rule( '/', view_func = InventoryView.as_view( 'view' ), defaults = {'action':'index'} )
bpIvt.add_url_rule( '/<action>', view_func = InventoryView.as_view( 'view' ) )





#===============================================================================
# form class
#===============================================================================

from wtforms import Form, TextField, SelectField


class SrhForm( Form ):
    ivtID = SelectField( u'所在仓位', choices = [] )
    no = TextField( u'商品编号', )
    barcode = TextField( u'商品条码', )
    name = TextField( u'商品名称', )
    styleNo = TextField( u'款号', )
    typeID = SelectField( u'类型', )
    seriesID = SelectField( u'系列', )
    shapID = SelectField( u'形状', )

    clarity = TextField( u'净度', )
    color = TextField( u'颜色', )
    goldFineness = TextField( u'金成色', )
    certificate = TextField( u'证书号', )
